﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Collections;
using System.Data.Sql;
using System.Data.Common;
using System.Data;

namespace AliboboStockLib
{
    public class SQLiteAgent : ISQLAgent
    {
          protected static  SQLiteAgent sqlAgent;
         SQLiteConnection sqliteConnection= null;
        public static SQLiteAgent getInstance()
        {
            if(sqlAgent==null)
                sqlAgent= new SQLiteAgent();
            return sqlAgent;
        }
        public  DbTransaction BeginTransation()
        {
            return getInstance().sqliteConnection.BeginTransaction();
        }
        protected SQLiteAgent()
        {
            String connString = @"data source=D:\sqlite\StockSBDetails\SBData.db;pooling=True;Read Only=False";
            sqliteConnection= new SQLiteConnection(connString);
            
            sqliteConnection.Open();
        }
        protected DbCommand getCommand_internal()
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = "";
            cmd.Connection =  getInstance().sqliteConnection;
            return cmd;
        }

        public DbParameter createParam(String name, DbType type, Object value)
        {
            SQLiteParameter p = new SQLiteParameter(name,type);
            p.Value = value;
            return p;
        }

        public  DbParameter createParam(String name,Object value)
        {
            SQLiteParameter p = new SQLiteParameter(name, value);
            return p;
        }
        public  DbCommand getCommand()
        {
            return getInstance().getCommand_internal();
        }
        public  DbCommand getCommand(String cmd)
        {
            DbCommand r=getInstance().getCommand_internal();
            if (r != null)
                r.CommandText = cmd;
            return r;
        }
        public void executeInsert(String sql, List<String> names, ArrayList ps)
        {

            DbCommand cmd = getCommand();
            cmd.CommandText = sql;
            if(names.Count!=ps.Count)
            {
                throw new Exception("Error in Execure insert , names size != parameters size");
            }
            int sz=names.Count;
            for(int i=0;i<sz;i++)
            {
                   cmd.Parameters.Add(createParam((String)names[i],ps[i]));
            }
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            cmd.Dispose();
        }
        public DbDataAdapter getAdapter(DbCommand cmd)
        {
            SQLiteDataAdapter adap = new SQLiteDataAdapter((SQLiteCommand)cmd);
            return adap;
        }

        ~SQLiteAgent()
        {
            
        }
    }
}
